/************************************************************************************************
File  : InstallASAssembly.sql
Author: Jocaplan
Date  : 04/22/2009
Decr  : this script Installs the assembly Microsoft.AnalysisServices
List of all CLRs:

********************************************************************************
** Change History
********************************************************************************
** Date        Author     Description
** ----------  ---------  -----------------------------------------------------
*******************************************************************************/
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
SET ANSI_WARNINGS ON
SET ANSI_PADDING ON
SET ANSI_NULLS ON
SET CONCAT_NULL_YIELDS_NULL ON

DECLARE @err int
       ,@rc  int
       ,@rows int
       ,@errordescr varchar(255)
       ,@AnalysisServicesAssembly varchar(8000)
       ,@SQLDB varchar(100)

SELECT @SQLDB = db_name() 

----------------------------------------------------------------------------------
-- Make 'show advanced options' enabled
----------------------------------------------------------------------------------
exec @rc = sp_configure 'show advanced options', 1
SELECT @Err = @@ERROR, @Rows = @@ROWCOUNT

IF @Err <> 0 OR @rc <> 0
BEGIN
   SELECT @errordescr = 'Failed running sp_configure to make show advanced options enabled.'
   GOTO ErrorHandler
END

RAISERROR('Successfully run sp_configure to make show advanced options enabled.', 0,1) WITH NOWAIT

----------------------------------------------------------------------------------
-- RECONFIGURE to make 'show advanced options' enabled
----------------------------------------------------------------------------------
RECONFIGURE WITH OVERRIDE
SELECT @err = @@ERROR
      ,@rows = @@ROWCOUNT
      ,@errordescr = 'Failed running RECONFIGURE to make show advanced options enabled.'

IF @err <> 0 GOTO ErrorHandler

RAISERROR('Successfully run RECONFIGURE to make show advanced options enabled.', 0,1) WITH NOWAIT

----------------------------------------------------------------------------------
-- Make xp_cmdshell enabled
----------------------------------------------------------------------------------
exec @rc = sp_configure 'xp_cmdshell', 1
SELECT @Err = @@ERROR, @Rows = @@ROWCOUNT

IF @Err <> 0 OR @rc <> 0
BEGIN
   SELECT @errordescr = 'Failed running sp_configure to make xp_cmdshell enabled.'
   GOTO ErrorHandler
END

RAISERROR('Successfully run sp_configure to make xp_cmdshell enabled.', 0,1) WITH NOWAIT

----------------------------------------------------------------------------------
-- RECONFIGURE to make xp_cmdshell enabled
----------------------------------------------------------------------------------
RECONFIGURE WITH OVERRIDE
SELECT @err = @@ERROR
      ,@rows = @@ROWCOUNT
      ,@errordescr = 'Failed running RECONFIGURE to make xp_cmdshell enabled.'

IF @err <> 0 GOTO ErrorHandler

RAISERROR('Successfully run RECONFIGURE to make xp_cmdshell enabled.', 0,1) WITH NOWAIT

----------------------------------------------------------------------------------
-- Make CLR enabled
----------------------------------------------------------------------------------
exec @rc = sp_configure 'clr enabled', 1
SELECT @Err = @@ERROR, @Rows = @@ROWCOUNT

IF @Err <> 0 OR @rc <> 0
BEGIN
   SELECT @errordescr = 'Failed running sp_configure to make CLR enabled.'
   GOTO ErrorHandler
END

RAISERROR('Successfully run sp_configure to make CLR enabled.', 0,1) WITH NOWAIT

----------------------------------------------------------------------------------
-- RECONFIGURE to make CLR enabled
----------------------------------------------------------------------------------
RECONFIGURE WITH OVERRIDE
SELECT @err = @@ERROR
      ,@rows = @@ROWCOUNT
      ,@errordescr = 'Failed running RECONFIGURE to make CLR enabled.'

IF @err <> 0 GOTO ErrorHandler

RAISERROR('Successfully run RECONFIGURE to make CLR enabled.', 0,1) WITH NOWAIT

----------------------------------------------------------------------------------
-- Make TRUSTWORTHY ON for this database
----------------------------------------------------------------------------------
EXEC ('ALTER DATABASE '+@SQLDB+' SET TRUSTWORTHY ON')
SELECT @err = @@ERROR
      ,@rows = @@ROWCOUNT
      ,@errordescr = 'Failed running ALTER DATABASE '+@SQLDB+' SET TRUSTWORTHY ON.'

IF @err <> 0 GOTO ErrorHandler

RAISERROR('Successfully ran ALTER DATABASE %s SET TRUSTWORTHY ON.', 0,1,@SQLDB) WITH NOWAIT

----------------------------------------------------------------------------------
-- Get Parameter to determine @AnalysisServicesAssembly
----------------------------------------------------------------------------------
                           
SELECT 
   @AnalysisServicesAssembly = ParamChar
FROM 
   Parameters
WHERE
   ParamName = 'AnalysisServicesAssembly'

----------------------------
SELECT @Err = @@ERROR, @Rows = @@ROWCOUNT
      
----------------------------
IF @Err <> 0 OR @rc <> 0
BEGIN
   SELECT @errordescr = 'Selecting from the parameters table failed.'
   GOTO ErrorHandler
END
---------------------------
IF @AnalysisServicesAssembly IS NULL OR @AnalysisServicesAssembly = ''
BEGIN
   SELECT @errordescr = 'Parameter @AnalysisServicesAssembly in parameters table can not be null or blank.'
   GOTO ErrorHandler
END



-----------------------------------------------------------------------
IF NOT EXISTS(SELECT * FROM sys.assemblies WHERE name = 'Microsoft.AnalysisServices')
BEGIN
   CREATE ASSEMBLY [Microsoft.AnalysisServices] 
   FROM @AnalysisServicesAssembly
   WITH permission_set=UNSAFE

   SELECT @err = @@ERROR
         ,@rows = @@ROWCOUNT
         ,@errordescr = 'Failed creating assembly Microsoft.AnalysisServices in the database.'

   IF @err <> 0 GOTO ErrorHandler

   RAISERROR('Successfully created assembly Microsoft.AnalysisServices in the database.', 0,1) WITH NOWAIT
END
ELSE
   RAISERROR('Assembly Microsoft.AnalysisServices already exists in the database.', 0,1) WITH NOWAIT

-----------------------------------------------------------------------------------------------------------------

GOTO NextStep

ErrorHandler:

RAISERROR('',0,1) WITH NOWAIT
RAISERROR(@errordescr, 18,127)
RAISERROR('Failed Running script InstallASAssembly.sql', 0,1) WITH NOWAIT

NextStep: